import pymysql

#自定义学生信息操作类
class mysql():

    #构造方法，连接SQL数据库
    def __init__(self):
       
        self.db = pymysql.connect(host="localhost",user="root",password="",db="mydb")
        self.cursor=self.db.cursor()

    #定义查询函数
    def findAll(self):
        try:
            self.cursor.execute("select * from stu;")
            if self.cursor.rowcount==0:
                print ("没有学员信息可以输出")
            else:
                print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format("sid","name","age","classid"))

                for i in self.cursor.fetchall():
                    print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format(i[0], i[1], i[2],i[3]))


        except Exception as err:
            self.db.rollback()
            print("查询失败，原因是：",err)

    #定义添加函数
    def insert(self,mydb):
        try:
           self.cursor.execute('insert into stu values(null,"{}","{}","{}");'.format(stu["name"],stu["age"],stu["classid"])) 
        except Exception as err:
            self.db.rollback()
            print("添加失败，原因是：",err)

    #定义删除函数
    def delete(self,id):
        try:
            self.cursor.execute("delete from stu where id={};".format(sid))

        except Exception as err:
            self.db.rollback()
            print("删除失败，原因是：",err)

    #析构方法，关闭数据库连接
    def __del__(self):
        self.cursor.close()
        self.db.close()


#建立操作类对象
m=mysql()

while True:
    # 输出初始界面
    print("="*12,"学员管理系统","="*14)
    print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
    print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
    print("="*40)
    key = input("请输入对应的选择：")
    # 根据键盘值，判断并执行对应的操作
    if key == "1":
        print("="*12,"学员信息浏览","="*14)
        m.findAll()
        input("按回车键继续：")
    elif key == "2":
        print("="*12,"学员信息添加","="*14)
        stu={}
        stu['name']=input("请输入要添加的姓名：")
        stu['age']=input("请输入要添加的年龄：")
        stu['classid']=input("请输入要添加的班级号：")
        m.insert(stu)
        m.findAll()
        input("按回车键继续：")
    elif key == "3":
        print("="*12,"学员信息删除","="*14)
        m.findAll()
        sid = input("请输入你要删除的信息id号：")
        m.delete(sid)
        m.findAll()
        input("按回车键继续：")
    elif key == "4":
        print("="*12,"再见","="*14)
        break
    else:
        print("======== 无效的键盘输入！ ==========")
        
    
     

        


